<html>
<META http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<head>
<title>Section 8.4.&nbsp; Advanced SQL</title>
<link rel="STYLESHEET" type="text/css" href="images/style.css">
<link rel="STYLESHEET" type="text/css" href="images/docsafari.css">
<script type="text/javascript"><!--
google_ad_client = "pub-0203281046321155";
google_alternate_ad_url = "http://www.bookhtml.com/adbrite.htm";
google_ad_width = 728;
google_ad_height = 90;
google_ad_format = "728x90_as";
google_ad_type = "text_image";
google_ad_channel ="4867465545";
google_color_border = "FFFFFF";
google_color_link = "0000FF";
google_color_bg = "FFFFFF";
google_color_text = "000000";
google_color_url = "0000FF";
//--></script>
<script type="text/javascript"
  src="http://pagead2.googlesyndication.com/pagead/show_ads.js">
</script>
</head>
<body>
<table width="100%" border="0" cellspacing="0" cellpadding="0">
<tr><td><div STYLE="MARGIN-LEFT: 0.15in;">
<a href=learnphpmysql-CHP-8-SECT-3.html><img src="images/prev.gif" width="60" height="17" border="0" align="absmiddle" alt="Previous Page"></a>
<td align="right"><div STYLE="MARGIN-LEFT: 0.15in;">
<a href=learnphpmysql-CHP-8-SECT-5.html><img src="images/next.gif" width="60" height="17" border="0" align="absmiddle" alt="Next Page"></a>
</div></td></tr></table>
<br><table width="100%" border="0" cellspacing="0" cellpadding="0"><tr><td valign="top"><a name="learnphpmysql-CHP-8-SECT-4"></a>
<h3 id="title-IDAWU4LM" class="docSection1Title">8.4. Advanced SQL</h3>
<p class="docText">In this section, we'll introduce database concepts that, while not strictly necessary for developing your web sites, can increase performance and give your queries more flexibility.</p>
<a name="learnphpmysql-CHP-8-SECT-4.1"></a>
<h4 id="title-IDA4U4LM" class="docSection2Title">8.4.1. Indexes</h4>
<p class="docText">Indexes work the same way that an index of a book works. If you were to look for the keyword "create table" without an index, you'd need to spend a lot of time scanning through the pages of the book looking for a section that might be relevant. Then you'd have to scan the entire section. This certainly isn't an efficient use of your time or the database's. The solution is an index at the end.</p>
<p class="docText">The data in an index is sorted in order and organized to make finding a specific value as quickly as possible. Because the values are sorted, if you're looking for something specific, the database can stop looking when it finds a value larger than the item you're looking for.</p>
<p class="docText">You face the same problems as a book does, though. If an index is so great, why not index everything? There are numerous reasons:</p>
<ul><li><p class="docList">There's only a finite amount of space available.</p></li><LI><p class="docList">When writing books, it becomes inefficient to generate and maintain a gigantic, all-encompassing index.</p></li></UL>
<p class="docText">So some intelligent decisions about which fields to index in your tables have to be made. Each index requires its own datafile for storage, which can add a bit of processing time when the contents of an indexed field changes in the database.</P>
<a name="learnphpmysql-CHP-8-SECT-4.1.1"></a>
<h5 id="title-IDAWV4LM" class="docSection3Title">8.4.1.1. When indexes are used</H5>
<p class="docText">If you do a simple <tt>SELECT</tt> statement with a <tt>WHERE</tt> clause, an index won't be used. There are three major areas where an index can be used:</p>
<dl class="docList">
<dt><br><p><span class="docPubcolor">In a <span class="docPubcolor"><span class="docMonofont">WHERE</span></span> clause</span></P></dt>
<dd><p class="docList">For example, the query <tt>SELECT * FROM `authors` WHERE `author` = 'Ellen Siever'</tt>; would use an index on the <tt>author</tt> column if it's available.</p></dd>

<dt><br><P><span class="docPubcolor">In an <span class="docPubcolor"><span class="docMonofont">ORDER BY</span></span> clause</span></p></dt>
<dd><p class="docList">For example, the query <tt>SELECT * FROM `contacts` ORDER BY `author`;</tt> would use an index on the <tt>author</tt> column if available.</p></dd>

<dt><BR><p><span class="docPubcolor">In <span class="docPubcolor"><span class="docMonofont">MIN</span></span> and <span class="docPubcolor"><span class="docMonofont">MAX</span></span> clauses</span></p></dt>
<dd><p class="docList">For example, if the column that is specified in the <tt>MIN</tt> or <tt>MAX</tt> function has an index.</p></dd>
</dl>
<p class="docText">Just remember, indexes have to be defined before they can be used.</p>

<a name="learnphpmysql-CHP-8-SECT-4.1.2"></a>
<H5 id="title-IDA1X4LM" class="docSection3Title">8.4.1.2. Where to specify the index</H5>
<p class="docText">Database indexes can be specified as part of the <tt>CREATE TABLE</tt> command or they can be added to an existing table by using special SQL commands. If the index is created as part of the <tt>CREATE TABLE</tt> command, then it's specified at the end of the code block like this:</p>
<pre>
UNIQUE `authind` (`author`)
</pre><br>

<p class="docText">This <tt>UNIQUE</tt> command creates an index on the <tt>author</tt> name field. To create the same index using a SQL statement, use the code in <a class="docLink" href="#learnphpmysql-CHP-8-EX-3">Example 8-3</a>.</p>
<a name="learnphpmysql-CHP-8-EX-3"></a><h5 id="title-IDAZY4LM" class="docExampleTitle">Example 8-3. Creating a simple index</H5><p><table cellspacing="0" width="90%" border="1" cellpadding="5"><TR><td>

<pre>
CREATE UNIQUE INDEX `authind` ON `authors` (`author`);
</pre><BR>

</TD></TR></table></p>
<p class="docText">which returns the following:</p>
<pre>
Query OK, 4 rows affected (0.11 sec)
Records: 4  Duplicates: 0  Warnings: 0
</pre><br>

<p class="docText">Now to describe the table:</p>
<pre>
DESCRIBE `authors`;
</pre><br>

<p class="docText">This gives you this information:</p>
<pre>
+-----------+--------------+------+-----+---------+----------------+
| Field     | Type         | Null | Key | Default | Extra          |
+-----------+--------------+------+-----+---------+----------------+
| author_id | int(11)      |      | PRI | NULL    | auto_increment |
| title_id  | int(11)      |      |     | 0       |                |
| author    | varchar(125) | YES  | UNI | NULL    |                |
+-----------+--------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)
</pre><br>

<p class="docText">Notice the new value of <tt>UNI</tt> in the <tt>key</tt> column for <tt>author</tt>.</p>

<a name="learnphpmysql-CHP-8-SECT-4.1.3"></a>
<h5 id="title-IDAYZ4LM" class="docSection3Title">8.4.1.3. Multicolumn indexes</h5>
<a name="IDX-CHP-8-0392"></a> 

<p class="docText">It's also possible to create MySQL indexes that use more than one column. A multicolumn<a name="IDX-CHP-8-0393"></a> 
 unique index can be used to make sure that the combination of two or more keys is unique.</p>
<p class="docText">The best columns to index are those that are likely to be used in the <tt>WHERE</tt> clause, especially if you know that certain combinations of keys will be used. Those are good columns to add to a multicolumn index.</p>
<p class="docText">Unique indexes, similar to primary indexes, are also unique. Only one primary index is allowed per table. However, you can have as many unique indexes as your heart desires; there is no parameter on this.</p>
<p class="docText">We're going to do a query with a specific <tt>WHERE</tt> clause and then use <tt>EXPLAIN</tt> to get details about how it was processed by MySQL:</p>
<pre>
SELECT * FROM `authors` WHERE `author` = 'Arnold Robbins';
</pre><BR>

<p class="docText">This returns:</p>
<pre>
+-----------+----------+----------------+
| author_id | title_id | author         |
+-----------+----------+----------------+
|         3 |        2 | Arnold Robbins |
+-----------+----------+----------------+
1 row in set (0.00 sec)
</pre><br>

<p class="docText">Use the <tt>EXPLAIN</tt> keyword on a database that doesn't have an index defined for the <tt>authors</tt> table:</P>
<pre>
EXPLAIN SELECT * `authors` WHERE `author` = 'Arnold Robbins';
</pre><BR>

<p class="docText"><tt>EXPLAIN</tt>, in turn, gives you this output:</p>
<pre>
+----+-------------+---------+------+---------------+------+---------+------+---
---+-------------+
| id | select_type | table   | type | possible_keys | key  | key_len | ref  | ro
ws | Extra       |
+----+-------------+---------+------+---------------+------+---------+------+---
---+-------------+
|  1 | SIMPLE      | authors | ALL  | NULL          | NULL |    NULL | NULL |
 4 | Using where |
+----+-------------+---------+------+---------------+------+---------+------+---
---+-------------+
1 row in set (0.00 sec)
</pre><BR>

<p class="docText">The <tt>EXPLAIN</tt> output provides a wealth of information about how MySQL processed the query.</p>
<p class="docText">It tells you that:</p>
<ul><LI><p class="docList">You're using the <tt>authors</tt> table.</p></li><LI><p class="docList">The query type is <tt>ALL</tt>, so every record is scanned to check for the correct value.</p></li><LI><p class="docList">The <tt>possible_keys</tt> is <tt>NULL</tt>, because no index matches.</p></li><li><p class="docList">The key used by this query; currently, none.</p></LI><LI><p class="docList">The <tt>key_len</tt> is the key length; currently, <tt>NULL</tt> as no key was used.</p></li><li><p class="docList">The <tt>ref</tt> column displays which columns or constants are used with the key; currently, none.</p></LI><li><p class="docList">The number of rows that must be searched through for this query.</P></li></UL>
<p class="docText">After creating a unique index on <tt>authors</tt> called <tt>authind</tt> using the syntax from <a class="docLink" href="#learnphpmysql-CHP-8-EX-3">Example 8-3</a>, rerun the <tt>EXPLAIN</tt> query:</P>
<pre>
+----+-------------+---------+-------+---------------+---------+---------+------
-+------+-------+
| id | select_type | table   | type  | possible_keys | key     | key_len | ref
 | rows | Extra |
+----+-------------+---------+-------+---------------+---------+---------+------
-+------+-------+
|  1 | SIMPLE      | authors | const | authind       | authind |     126 | const
 |    1 |       |
+----+-------------+---------+-------+---------------+---------+---------+------
-+------+-------+
1 row in set (0.12 sec)
</pre><BR>

<p class="docText">Notice that many of the values have changed regarding the indexing.</p>
<p class="docText">Typing <tt>ref</tt> would mean that rows with matching index values are read from this table for matches.</p>
<ul><li><p class="docList"><tt>possible_keys</tt> displays a possible key of <tt>authind</tt>.</p></li><li><p class="docList"><tt>key</tt> displays that the <tt>authind</tt> key was used.</p></li><li><p class="docList"><tt>key_len</tt> displays the length of the key as 126.</p></li><li><p class="docList"><tt>ref</tt> tells you that a constant key is being used.</p></LI><li><p class="docList"><tt>rows</tt> shows that one row was searched, which is much less than before.</p></LI></UL>
<p class="docText">The comparison shows that adding the index saves a lot of processing time, even for this small table.</p>


<a name="learnphpmysql-CHP-8-SECT-4.2"></a>
<H4 id="title-IDA244LM" class="docSection2Title">8.4.2. Selecting Using the LEFT JOIN ON Clause</h4>
<a name="IDX-CHP-8-0394"></a> 
<a name="IDX-CHP-8-0395"></a> 
<a name="IDX-CHP-8-0396"></a> 
<a name="IDX-CHP-8-0397"></a> 
<a name="IDX-CHP-8-0398"></a> 

<p class="docText">We've discussed performing joins in our <tt>SELECT</tt> statements using the <tt>WHERE</tt> clause, but there's another way to join tables. Instead of using the <tt>WHERE</tt> keyword, <tt>LEFT JOIN ON</tt> can be used to perform a <span class="docEmphasis">left</span> or <span class="docEmphasis">outer join</span>. A left join simply allows you to query two tables that are linked together by a relationship but allows one of the tables to return rows even if there isn't a matching row in the other table. Using the bookstore tables as an example, you might want to create a query that returns users and their purchases but also lists users who have yet to purchase anything.</p>
<p class="docText">Using the syntax:</p>
<pre>
SELECT <tt><I>fields</i></tt> FROM <tt><i>left_table</I></tt> LEFT JOIN <tt><i>right_table</i></tt> ON <tt><I>left_table.field</i></tt>_id = <tt><i>right_table.field_id</i></tt>;
</pre><br>

<p class="docText">your goal could be accomplished like this:</P>
<pre>
SELECT * FROM `users` LEFT JOIN `purchases` ON `users`.`user_id` =
`purchases`.`user_id`;
</pre><BR>

<p class="docText">When doing a normal database query that links two tables, if both tables do not include the key values for the field being joined, nothing is returned for the entry.</p>

<a name="learnphpmysql-CHP-8-SECT-4.3"></a>
<h4 id="title-IDADB5LM" class="docSection2Title">8.4.3. Using Database Functions</h4>
<p class="docText">Just like there are functions in PHP, you can also use functions within your MySQL queries. We'll discuss several categories of functions starting with string functions.<a name="IDX-CHP-8-0399"></a> 
<a name="IDX-CHP-8-0400"></a> 
 The other major categories you'll learn about are date and time modification functions.</p>
<a name="learnphpmysql-CHP-8-SECT-4.3.1"></a>
<H5 id="title-IDAWB5LM" class="docSection3Title">8.4.3.1. String functions</h5>
<p class="docText">Since you'll frequently work with strings, MySQL provides many functions for doing a variety of tasks. You'll generally use the string functions with data that is being returned from a query. However, it's possible to use them without even referencing a table.</P>
<a name="learnphpmysql-CHP-8-SECT-4.3.1.1"></a>
<h5 id="title-IDA4B5LM" class="docSection4Title">8.4.3.1.1. Concatenation</H5>
<p class="docText">Just like the process of putting strings together with the PHP operator (<tt>.</tt>), which is a period, MySQL can paste together strings from data fields with the <tt>CONCAT</tt> function.</P>
<p class="docText">For example, if you want to return a single field that combines the title with the number of pages, you could use <tt>CONCAT</tt>. <a class="docLink" href="#learnphpmysql-CHP-8-EX-4">Example 8-4</a> shows how this is done.</P>
<a name="learnphpmysql-CHP-8-EX-4"></a><h5 id="title-IDAVC5LM" class="docExampleTitle">Example 8-4. Using CONCAT to put fields together</h5><p><table cellspacing="0" width="90%" border="1" cellpadding="5"><tr><td>

<pre>
SELECT <b>CONCAT</b>(`title`,' has ',`pages`,' pages.') FROM `books`;
</pre><br>

</td></tr></table></p>
<p class="docText">Concatenating returns:</p>
<pre>
+----------------------------------------+
| concat(title,' has ',pages,' pages.')  |
+----------------------------------------+
| Linux in a Nutshell has 476 pages.     |
| Classic Shell Scripting has 256 pages. |
+----------------------------------------+
2 rows in set (0.02 sec)
</pre><br>

<p class="docText">The result is a string that's ready for displaying straight from the SQL query.</p>
<p><table border="0" bgcolor="black" cellspacing="0" cellpadding="1" width="90%" align="center"><TR><td><table bgcolor="white" width="100%" border="0" cellspacing="0" cellpadding="6"><tr><TD width="60" valign="top"><img src="images/tip_yellow.jpg" width="50" height="54" alt=""></TD><td valign="top">
<p class="docText">When using field names in functions, don't enclose them in single or double quotes. MySQL will interpret them as literal text like the string <tt>' has '</tt> in <a class="docLink" href="#learnphpmysql-CHP-8-EX-4">Example 8-4</a>.</P>
</td></tr></table></td></TR></table></p><br>
<p class="docText">The <tt>CONCAT</tt> function pastes together as many fields as you give it.</P>

<a name="learnphpmysql-CHP-8-SECT-4.3.1.2"></a>
<h5 id="title-IDA2D5LM" class="docSection4Title">8.4.3.1.2. Concatenation with a predefined separator</h5>
<a name="IDX-CHP-8-0401"></a> 

<p class="docText">Sometimes you might want to consistently put the same character or string between fields you're concatenating. This can be used for building a table export list. To do this, use the <tt>CONCAT_WS</tt> function.</P>
<p class="docText">For example, to return all of the fields in the <tt>authors</tt> table with commas as separators, you would use:</p>
<pre>
SELECT CONCAT_WS(',',`author_id`,`title_id`,`author`) FROM `authors`;
</pre><br>

<p class="docText">This returns the following:</p>
<pre>
+------------------------------------------+
| CONCAT_WS(',',author_id,title_id,author) |
+------------------------------------------+
| 1,1,Ellen Siever                         |
| 2,1,Aaron Weber                          |
| 3,2,Arnold Robbins                       |
| 4,2,Nelson Beebe                         |
+------------------------------------------+
4 rows in set (0.01 sec)
</pre><br>

<p class="docText">The separator could have been a space, which is useful for putting first and last name fields together for display.</P>

<a name="learnphpmysql-CHP-8-SECT-4.3.1.3"></a>
<H5 id="title-IDA1E5LM" class="docSection4Title">8.4.3.1.3. Calculate a string length</h5>
<p class="docText">To calculate the length of a string, use the <tt>LENGTH</tt> function, as shown in <a class="docLink" href="#learnphpmysql-CHP-8-EX-5">Example 8-5</a>.</p>
<a name="learnphpmysql-CHP-8-EX-5"></a><h5 id="title-IDALF5LM" class="docExampleTitle">Example 8-5. Calculating the length of a string</h5><P><table cellspacing="0" width="90%" border="1" cellpadding="5"><tr><TD>

<pre>
SELECT CONCAT(`title`,' has '<b>,LENGTH(`title`),</b> ' characters.') FROM `books`;
</pre><BR>

</TD></TR></table></p>
<p class="docText">This returns:</p>
<pre>
+-----------------------------------------------------+
| CONCAT(title,' has ',LENGTH(title), ' characters.') |
+-----------------------------------------------------+
| Linux in a Nutshell has 19 characters.              |
| Classic Shell Scripting has 23 characters.          |
+-----------------------------------------------------+
2 rows in set (0.02 sec)
</pre><br>

<p class="docText"><a class="docLink" href="#learnphpmysql-CHP-8-EX-5">Example 8-5</a> shows the usage of <tt>LENGTH</tt> and <tt>CONCAT</tt> together.</p>

<a name="learnphpmysql-CHP-8-SECT-4.3.1.4"></a>
<h5 id="title-IDAHG5LM" class="docSection4Title">8.4.3.1.4. Changing strings to upper- or lowercase</h5>
<a name="IDX-CHP-8-0402"></a> 
<a name="IDX-CHP-8-0403"></a> 
<a name="IDX-CHP-8-0404"></a> 
<a name="IDX-CHP-8-0405"></a> 
<a name="IDX-CHP-8-0406"></a> 

<p class="docText">If you want to change the case of a string to all upper- or lowercase letters, you can use the <tt>UCASE</tt> and <tt>LCASE</tt> functions. For example, to covert the book title to all uppercase and then all lowercase, use the code in <a class="docLink" href="#learnphpmysql-CHP-8-EX-6">Example 8-6</a>.</p>
<a name="learnphpmysql-CHP-8-EX-6"></a><h5 id="title-IDARH5LM" class="docExampleTitle">Example 8-6. Changing the case of the title</h5><p><table cellspacing="0" width="90%" border="1" cellpadding="5"><tr><td>

<pre>
SELECT <b>UCASE</b>(`title`), <b>LCASE</b>(`title`) from `books`;
</pre><BR>

</td></tr></table></P>
<p class="docText"><a class="docLink" href="#learnphpmysql-CHP-8-EX-6">Example 8-6</a> returns:</P>
<pre>
+-------------------------+-------------------------+
| UCASE(title)            | LCASE(title)            |
+-------------------------+-------------------------+
| LINUX IN A NUTSHELL     | linux in a nutshell     |
| CLASSIC SHELL SCRIPTING | classic shell scripting |
+-------------------------+-------------------------+
2 rows in set (0.03 sec)
</pre><br>


<a name="learnphpmysql-CHP-8-SECT-4.3.1.5"></a>
<H5 id="title-IDAMI5LM" class="docSection4Title">8.4.3.1.5. Trimming and padding strings</h5>
<a name="IDX-CHP-8-0407"></a> 
<a name="IDX-CHP-8-0408"></a> 

<p class="docText">When working with forms, it's sometimes necessary to pad the length of a string to improve its display. The padding can be dots or some other character. <tt>VARCHAR</tt> type strings, in particular, are variable in length. The two functions that perform padding are <tt>LPAD</tt> and <tt>RPAD</tt>, they pad from the left and right, respectively. They both take three arguments: the string to pad, the size of the pad, and what character to use as padding. For example, we'll do a left pad on the <tt>title</tt> field of <tt>books</tt> to make it a uniform 30 characters with a period (<tt>.</tt>) as the padding character:</p>
<pre>
SELECT LPAD(`title`,30,'.') FROM `books`;
</pre><br>

<p class="docText">This returns your values all at the righthand margin:</P>
<pre>
+--------------------------------+
| LPAD(title,30,'.')             |
+--------------------------------+
| ...........Linux in a Nutshell |
| .......Classic Shell Scripting |
+--------------------------------+
2 rows in set (0.00 sec)
</pre><br>

<p class="docText">This looks somewhat like the formatting you see in a table of contents.</p>
<p class="docText">To trim spaces or tabs (also known as whitespace) from a string, use <tt>LTRIM</tt> to remove them from the left and <tt>RTRIM</tt> to remove them from the right.</P>
<p class="docText">To trim nonwhitespace characters, use the <tt>trIM</tt> function. It uses a syntax that's slightly different, because you're leading trimming:</p>
<pre>
TRIM(LEADING FROM <tt><I>string</i></tt>);
</pre><br>

<p class="docText">For trailing trimming, use:</p>
<pre>
TRIM(TRAILING FROM <tt><i>string</I></tt>);
</pre><BR>

<p class="docText">In <a class="docLink" href="#learnphpmysql-CHP-8-EX-7">Example 8-7</a>, <tt>LEADING</tt> is used to remove the leading zeros.</p>
<a name="learnphpmysql-CHP-8-EX-7"></a><h5 id="title-IDAVK5LM" class="docExampleTitle">Example 8-7. Using the LEADING option to remove zeros</h5><p><table cellspacing="0" width="90%" border="1" cellpadding="5"><TR><td>

<pre>
SELECT TRIM(LEADING '0' from '0000Example00000');
</pre><BR>

</td></TR></table></P>
<p class="docText"><a class="docLink" href="#learnphpmysql-CHP-8-EX-7">Example 8-7</a> returns:</P>
<pre>
+-------------------------------------------+
| TRIM(LEADING '0' from '0000Example00000') |
+-------------------------------------------+
| Example00000                              |
+-------------------------------------------+
1 row in set (0.00 sec)
</pre><br>

<p class="docText">To remove the trailing zeros, use the code in <a class="docLink" href="#learnphpmysql-CHP-8-EX-8">Example 8-8</a>.</p>
<a name="learnphpmysql-CHP-8-EX-8"></a><h5 id="title-IDAPL5LM" class="docExampleTitle">Example 8-8. Using TRIM with the TRAILING option</h5><p><table cellspacing="0" width="90%" border="1" cellpadding="5"><tr><td>

<pre>
SELECT TRIM(TRAILING '0' from '0000Example00000');
</pre><br>

</td></tr></table></p>
<p class="docText"><a class="docLink" href="#learnphpmysql-CHP-8-EX-8">Example 8-8</a> returns:</p>
<pre>
+--------------------------------------------+
| TRIM(TRAILING '0' from '0000Example00000') |
+--------------------------------------------+
| 0000Example                                |
+--------------------------------------------+
1 row in set (0.01 sec)
</pre><br>

<p class="docText">Notice that while <a class="docLink" href="#learnphpmysql-CHP-8-EX-7">Examples 8-7</a> and <a class="docLink" href="#learnphpmysql-CHP-8-EX-8">8-8</a> don't reference any tables in the <tt>SELECT</tt> statements, they're still valid queries.</p>

<a name="learnphpmysql-CHP-8-SECT-4.3.1.6"></a>
<H5 id="title-IDARM5LM" class="docSection4Title">8.4.3.1.6. String location and position</h5>
<a name="IDX-CHP-8-0409"></a> 
<a name="IDX-CHP-8-0410"></a> 

<p class="docText">Sometimes you'll want to know whether a string is within a string and what its position is in that string. To locate a string within a string, use the <tt>LOCATE()</tt> function. It takes the string to look for and the string to search in as its arguments. <a class="docLink" href="#learnphpmysql-CHP-8-EX-9">Example 8-9</a> shows how the location of a string is returned from a database field.</p>
<a name="learnphpmysql-CHP-8-EX-9"></a><H5 id="title-IDANN5LM" class="docExampleTitle">Example 8-9. Looking for the string in our author names</H5><p><table cellspacing="0" width="90%" border="1" cellpadding="5"><TR><td>

<pre>
SELECT LOCATE`author`,LOCATE('on',`author`) FROM `authors`;
</pre><br>

</td></TR></table></p>
<p class="docText"><a class="docLink" href="#learnphpmysql-CHP-8-EX-9">Example 8-9</a> returns:</p>
<pre>
+-------------------+---------------------+
| author            | LOCATE(on',`author`)|
+-------------------+---------------------+
| Aaron Weber       |                   4 |
| Arnold Robbins    |                   0 |
| Ellen Siever      |                   0 |
| Nelson  Beebe     |                   5 |
+-------------------+---------------------+
4 rows in set (0.01 sec)
</pre><BR>

<p class="docText">The author names that don't contain the string <tt>on</tt> return a position of 0, indicating that the string was not found.</p>
<p><table border="0" bgcolor="black" cellspacing="0" cellpadding="1" width="90%" align="center"><TR><td><table bgcolor="white" width="100%" border="0" cellspacing="0" cellpadding="6"><tr><td width="60" valign="top"><img src="images/warning_yellow.jpg" width="51" height="36" alt=""></td><TD valign="top">
<p class="docText">The position counting for a match starts at 1, not 0, as with arrays in PHP. This is fortunate, since it would otherwise be impossible to tell the difference between a match at the beginning of the string and no match at all.</P>
</td></tr></table></td></tr></table></P><br>
<p class="docText">Keep in mind only the first occurrence of a string is matched, similar to a Find in an application. <tt>LOCATE()</tt> can also take a third argument to start looking at a position other than the start of the string.</P>

<a name="learnphpmysql-CHP-8-SECT-4.3.1.7"></a>
<h5 id="title-IDARO5LM" class="docSection4Title">8.4.3.1.7. Cutting up strings</H5>
<p class="docText">The substring functions provide a way to extract a portion of a string. All that's needed is the string to work with, the position to start from, and how many characters to extract. Use the <tt>LEFT, RIGHT</tt>, and <tt>SUBSTRING</tt> functions to do the extraction.</P>
<dl class="docList">
<dt><BR><p><span class="docPubcolor"><span class="docPubcolor"><span class="docMonofont">LEFT</span></span></span></p></dt>
<dd><p class="docList">Takes the string and the number of characters to extract from the start of the string.</p></dd>

<dt><br><p><span class="docPubcolor"><span class="docPubcolor"><span class="docMonofont">RIGHT</span></span></span></p></dt>
<dd><p class="docList">Takes the string and the number of characters to extract from the end of the string.</p></dd>

<dt><br><p><span class="docPubcolor"><span class="docPubcolor"><span class="docMonofont">SUBSTR</span></span></span></p></dt>
<dd><p class="docList">Takes the string and the number of characters to extract beginning with a certain position in the string.</p></dd>
</dl>
<p class="docText">For example, if a database has phone numbers stored in a 10-digit string without any formatting, the numbers could be displayed with the formatting by using the code in <a class="docLink" href="#learnphpmysql-CHP-8-EX-10">Example 8-10</a>.</p>
<a name="learnphpmysql-CHP-8-EX-10"></a><h5 id="title-IDAHQ5LM" class="docExampleTitle">Example 8-10. Adding the formatting to a phone number using LEFT, RIGHT,and SUBSTR</h5><P><table cellspacing="0" width="90%" border="1" cellpadding="5"><tr><td>

<pre>
SELECT CONCAT('(',
              LEFT('6128238193',3),
              ')',
           SUBSTR('6128238193',4,3),
              '-',
              RIGHT('6128238193', 4));
</pre><BR>

</TD></tr></table></P>
<p class="docText">These commands return:</p>
<pre>
+-------------------------------------------------------------------------------
-----------+
| CONCAT('(',LEFT('6128238193',3),')',SUBSTR('6128238193',4,3),'-',RIGHT('612823
8193', 4)) |
+-------------------------------------------------------------------------------
-----------+
| (612)823-8193
           |
+-------------------------------------------------------------------------------
-----------+
1 row in set (0.02 sec)
</pre><br>

<p class="docText"><a class="docLink" href="#learnphpmysql-CHP-8-EX-10">Example 8-10</a> shows how all three of these functions work together to reformat a phone number. The phone number could just as easily have been a database field instead of the number in the example.</p>

<a name="learnphpmysql-CHP-8-SECT-4.3.1.8"></a>
<H5 id="title-IDA2Q5LM" class="docSection4Title">8.4.3.1.8. Search and replace function</h5>
<a name="IDX-CHP-8-0411"></a> 
<a name="IDX-CHP-8-0412"></a> 

<p class="docText">Another useful function is the <tt>REPLACE</tt> function. It does what the name implies, exactly like find/replace in a word-processing application. It takes a source string, a search string, and a replacement string and returns the string with the replacement.</p>
<p class="docText">For example, suppose you wanted to replace "Avenue" with "Ave" in an address, but only for the current query. Here's how it's done:</P>
<pre>
SELECT REPLACE('2323 Fulerton Avenue', 'Avenue', 'Ave.');
</pre><br>

<p class="docText">The <tt>REPLACE</tt> function displays:</p>
<pre>
+---------------------------------------------------+
| REPLACE('2323 Fulerton Avenue', 'Avenue', 'Ave.') |
+---------------------------------------------------+
| 2323 Fulerton Ave.                                |
+---------------------------------------------------+
1 row in set (0.00 sec)
</pre><BR>

<p class="docText">Now that we've shown you just about all you could imagine you'll do with strings,<a name="IDX-CHP-8-0413"></a> 
 it's time to work with dates and times.</p>


<a name="learnphpmysql-CHP-8-SECT-4.3.2"></a>
<h5 id="title-IDAHS5LM" class="docSection3Title">8.4.3.2. Date and time functions</h5>
<p class="docText">Again, you're dealing with territory that PHP had functions to work with, but what if you'd like to query for purchases from the last 30 days? It's nice to be able to do the date and time arithmetic in the query. The date and time functions<a name="IDX-CHP-8-0414"></a> 
 can be used with or without a database table in the query. We'll show you both in the following examples.</p>
<a name="learnphpmysql-CHP-8-SECT-4.3.2.1"></a>
<H5 id="title-IDAVS5LM" class="docSection4Title">8.4.3.2.1. Days, months, years, and weeks</H5>
<p class="docText">Given a certain date, it's hard to remember if that day was a Tuesday or a Thursday. MySQL provides functions that tell you without having to do any of the thinking ourselves. How convenient! You could plot what day you were born just by establishing the date and year. PHP provides two very similar functions to do the calculation.</p>
<p class="docText">The <tt>WEEKDAY</tt> function takes a date as its argument and returns a number. The number represents the day of the week with Monday being 0. You could also use the <tt>DAYOFWEEK</tt> function, which, confusingly enough, does exactly the same thing but numbers the days differently, starting with Saturday as 1. <a class="docLink" href="#learnphpmysql-CHP-8-TABLE-3">Table 8-3</a> lists how each function numbers days of the week.</p>
<a name="learnphpmysql-CHP-8-TABLE-3"></a><p><table cellspacing="0" FRAME="hsides" RULES="all" cellpadding="4" width="100%"><caption><h5 class="docTableTitle">Table 8-3. WEEKDAY versus DAYOFWEEK</H5></caption><colgroup span="3"><col><col><col></colgroup><thead><tr><th class="thead" scope="col" align="left"><p class="docText">WEEKDAY value</P></th><th class="thead" scope="col" align="left"><p class="docText">DAYOFWEEK value</p></th><th class="thead" scope="col" align="left"><p class="docText">Day of the week</P></th></TR></thead><TR><td class="docTableCell" align="left"><p class="docText">0</p></td><td class="docTableCell" align="left"><p class="docText">2</p></td><td class="docTableCell" align="left"><p class="docText">Monday</p></td></tr><tr><td class="docTableCell" align="left"><p class="docText">1</p></td><TD class="docTableCell" align="left"><p class="docText">3</p></td><TD class="docTableCell" align="left"><p class="docText">Tuesday</P></td></TR><tr><td class="docTableCell" align="left"><p class="docText">2</p></TD><td class="docTableCell" align="left"><p class="docText">4</p></TD><td class="docTableCell" align="left"><p class="docText">Wednesday</p></TD></tr><tr><td class="docTableCell" align="left"><p class="docText">3</p></TD><TD class="docTableCell" align="left"><p class="docText">5</p></td><td class="docTableCell" align="left"><p class="docText">Thursday</p></TD></tr><TR><td class="docTableCell" align="left"><p class="docText">4</P></TD><TD class="docTableCell" align="left"><p class="docText">6</p></td><td class="docTableCell" align="left"><p class="docText">Friday</p></td></tr><tr><td class="docTableCell" align="left"><p class="docText">5</p></td><td class="docTableCell" align="left"><p class="docText">7</p></td><td class="docTableCell" align="left"><p class="docText">Saturday</P></td></tr><TR><TD class="docTableCell" align="left"><p class="docText">6</p></TD><td class="docTableCell" align="left"><p class="docText">1</p></td><TD class="docTableCell" align="left"><p class="docText">Sunday</p></td></TR></table></p><br>
<p class="docText">For example, to find out what day of the week was October 12, 1964, use the <tt>WEEKDAY</tt> function in <a class="docLink" href="#learnphpmysql-CHP-8-EX-11">Example 8-11</a>.</P>
<a name="learnphpmysql-CHP-8-EX-11"></a><h5 id="title-IDANY5LM" class="docExampleTitle">Example 8-11. Using WEEKDAY to get the day of the week</h5><p><table cellspacing="0" width="90%" border="1" cellpadding="5"><tr><TD>

<pre>
SELECT WEEKDAY('1964-10-12');
</pre><BR>

</td></tr></table></p>
<p class="docText">This then tells you:</p>
<pre>
+-----------------------+
| WEEKDAY('1964-10-12') |
+-----------------------+
|                     0 |
+-----------------------+
1 row in set (0.00 sec)
</pre><BR>

<p class="docText">Which means October 12, 1964 was a Monday. Pretty cool stuff!</p>
<p class="docText">It may seem a bit odd to return a number for the day of the week, so there's a function to return the day as its name. The <tt>DAYNAME</tt> function works like <tt>DAYOFWEEK</tt> or <tt>WEEKDAY</tt> but returns a string with the name instead, as shown in <a class="docLink" href="#learnphpmysql-CHP-8-EX-12">Example 8-12</a>.</P>
<a name="learnphpmysql-CHP-8-EX-12"></a><h5 id="title-IDAKZ5LM" class="docExampleTitle">Example 8-12. Using DAYNAME to get the day of the week as a name</H5><P><table cellspacing="0" width="90%" border="1" cellpadding="5"><TR><td>

<pre>
SELECT DAYNAME('1964-10-12');
</pre><br>

</td></tr></table></p>
<p class="docText">As you can see, an alpha answer returns:</p>
<pre>
+-----------------------+
| DAYNAME('1964-10-12') |
+-----------------------+
| Monday                |
+-----------------------+
1 row in set (0.00 sec)
</pre><br>

<p class="docText">Which proves that we were right in <a class="docLink" href="#learnphpmysql-CHP-8-EX-11">Example 8-11</a>!</p>
<p class="docText">Similar to the <tt>DAYOFWEEK</tt> function are <tt>DAYOFMONTH</tt> and <tt>DAYOFYEAR</tt>. They take a date as their input and return a number. <tt>DAYOFMONTH</tt> returns the day of the month, and <tt>DAYOFYEAR</tt> returns days since the beginning of the calendar year, as demonstrated in <a class="docLink" href="#learnphpmysql-CHP-8-EX-13">Example 8-13</a>.</p>
<a name="learnphpmysql-CHP-8-EX-13"></a><h5 id="title-IDAR05LM" class="docExampleTitle">Example 8-13. Finding days since the start of the year</h5><p><table cellspacing="0" width="90%" border="1" cellpadding="5"><tr><td>

<pre>
SELECT DAYOFYEAR('2006-1-1'),
       DAYOFYEAR('2006-12-24');
</pre><BR>

</td></tr></table></P>
<p class="docText">From your <tt>DAYOFYEAR</tt> function, it returns the following:</P>
<pre>
+-----------------------+-------------------------+
| DAYOFYEAR('2006-1-1') | DAYOFYEAR('2006-12-24') |
+-----------------------+-------------------------+
|                     1 |                     358 |
+-----------------------+-------------------------+
1 row in set (0.00 sec)
</pre><br>

<p class="docText">Just like the relationship between <tt>DAYOFWEEK</tt> and <tt>DAYNAME, MONTH</tt> and <tt>MONTHNAME</tt> return the numeric month or its name, as shown in <a class="docLink" href="#learnphpmysql-CHP-8-EX-14">Example 8-14</a>.</P>
<a name="learnphpmysql-CHP-8-EX-14"></a><h5 id="title-IDAP15LM" class="docExampleTitle">Example 8-14. Using MONTH and MONTHNAME on the purchases table</h5><p><table cellspacing="0" width="90%" border="1" cellpadding="5"><TR><td>

<pre>
SELECT `day`,MONTH(`day`),MONTHNAME(`day`) FROM `purchases`;
</pre><br>

</TD></tr></table></p>
<p class="docText"><a class="docLink" href="#learnphpmysql-CHP-8-EX-14">Example 8-14</a> returns:</P>
<pre>
+------------+------------+----------------+
| day        | MONTH(`day`) | MONTHNAME(`day`) |
+------------+------------+----------------+
| 2005-02-15 |          2 | February       |
| 2005-02-10 |          2 | February       |
+------------+------------+----------------+
2 rows in set (0.00 sec)
</pre><br>

<p class="docText">If you want to find the week number for a certain date, you can use the <tt>WEEK</tt> function. It takes a date as its argument and returns the week number.</p>
<pre>
SELECT WEEK('2006-12-24');
</pre><br>

<p class="docText">This returns:</p>
<pre>
+--------------------+
| WEEK('2006-12-24') |
+--------------------+
|                 52 |
+--------------------+
1 row in set (0.00 sec)
</pre><BR>

<p class="docText">This probably seems pretty easy compared to a lot of the information we've provided. Remember, though, based on how the calendar lays out, some years can have 53 weeks.</P>

<a name="learnphpmysql-CHP-8-SECT-4.3.2.2"></a>
<h5 id="title-IDAP25LM" class="docSection4Title">8.4.3.2.2. Hours, minutes, and seconds</h5>
<p class="docText">When working with <tt>datetime, timestamp</tt>, or <tt>time</tt> data types, a specific time is stored in the field. MySQL provides several functions to manipulate these times. They take the logical names: <tt>HOUR, MINUTE</tt>, and <tt>SECOND. HOUR</tt> takes a time as an argument and returns the hour from 0 to 23. <tt>MINUTE</tt> returns the minute of a time from 0 to 59, and similarly, <tt>SECOND</tt> returns the second in the same range, as shown in <a class="docLink" href="#learnphpmysql-CHP-8-EX-15">Example 8-15</a>.</p>
<a name="learnphpmysql-CHP-8-EX-15"></a><h5 id="title-IDAJ35LM" class="docExampleTitle">Example 8-15. Using HOUR and MINUTE on a time</H5><p><table cellspacing="0" width="90%" border="1" cellpadding="5"><TR><td>

<pre>
SELECT CONCAT_WS(':',hour('4:46:45'),MINUTE('4:46:45'));
</pre><BR>

</TD></TR></table></p>
<p class="docText"><a class="docLink" href="#learnphpmysql-CHP-8-EX-15">Example 8-15</a> returns:</p>
<pre>
+--------------------------------------------------+
| CONCAT_WS(':',hour('4:46:45'),MINUTE('4:46:45')) |
+--------------------------------------------------+
| 4:46                                             |
+--------------------------------------------------+
</pre><br>


<a name="learnphpmysql-CHP-8-SECT-4.3.2.3"></a>
<h5 id="title-IDA235LM" class="docSection4Title">8.4.3.2.3. Dates and times arithmetic</h5>
<p class="docText">MySQL provides the functions <tt>DATE_ADD</tt> and <tt>DATE_SUB</tt> to allow you to add and subtract days from dates. Their syntax is:</p>
<pre>
DATE_ADD(<tt><i>date</i></tt>,INTERVAL <tt><i>expression type</i></tt>)
DATE_SUB(<tt><i>date</i></tt>,INTERVAL <tt><i>expression type</i></tt>)
</pre><BR>

<p class="docText">The type can be one of those listed in <a class="docLink" href="#learnphpmysql-CHP-8-TABLE-4">Table 8-4</a>.</p>
<a name="learnphpmysql-CHP-8-TABLE-4"></a><p><table cellspacing="0" FRAME="hsides" RULES="all" cellpadding="4" width="100%"><caption><H5 class="docTableTitle">Table 8-4. Types and their corresponding expected values</H5></caption><colgroup span="2"><col><col></colgroup><thead><tr><th class="thead" scope="col" align="left"><p class="docText">Type</P></th><th class="thead" scope="col" align="left"><p class="docText">Value that is expected</p></th></tr></thead><tr><TD class="docTableCell" align="left"><p class="docText"><tt>MICROSECOND</tt></p></td><TD class="docTableCell" align="left"><p class="docText">Number of <tt>MICROSECONDS</tt></p></td></TR><tr><td class="docTableCell" align="left"><p class="docText"><tt>SECOND</tt></p></td><TD class="docTableCell" align="left"><p class="docText">Number of <tt>SECONDS</tt></P></td></tr><tr><td class="docTableCell" align="left"><p class="docText"><tt>MINUTE</tt></P></td><TD class="docTableCell" align="left"><p class="docText">Number of <tt>MINUTES</tt></p></TD></TR><TR><td class="docTableCell" align="left"><p class="docText"><tt>DAY</tt></p></td><td class="docTableCell" align="left"><p class="docText">Number of <tt>DAYS</tt></p></td></tr><tr><td class="docTableCell" align="left"><p class="docText"><tt>WEEK</tt></p></td><td class="docTableCell" align="left"><p class="docText">Number of <tt>WEEKS</tt></p></td></TR><tr><td class="docTableCell" align="left"><p class="docText"><tt>MONTH</tt></P></TD><td class="docTableCell" align="left"><p class="docText">Number of <tt>MONTHS</tt></P></td></tr><tr><TD class="docTableCell" align="left"><p class="docText"><tt>QUARTER</tt></p></td><TD class="docTableCell" align="left"><p class="docText">Number of <tt>QUARTERS</tt></p></td></TR><tr><td class="docTableCell" align="left"><p class="docText"><tt>YEAR</tt></p></td><TD class="docTableCell" align="left"><p class="docText">Number of <tt>YEARS</tt></P></td></tr><tr><td class="docTableCell" align="left"><p class="docText"><tt>SECOND_MICROSECOND</tt></P></td><TD class="docTableCell" align="left"><p class="docText"><tt><i>SECONDS.MICROSECONDS</I></tt></P></TD></tr><tr><td class="docTableCell" align="left"><p class="docText"><tt>MINUTE_MICROSECOND</tt></p></td><td class="docTableCell" align="left"><p class="docText"><tt><i>MINUTES.MICROSECONDS</i></tt></p></td></tr><tr><td class="docTableCell" align="left"><p class="docText"><tt>MINUTE_SECOND</tt></p></TD><td class="docTableCell" align="left"><p class="docText"><tt><i>MINUTES:SECONDS</I></tt></P></td></TR><tr><td class="docTableCell" align="left"><p class="docText"><tt>HOUR_MICROSECOND</tt></p></TD><td class="docTableCell" align="left"><p class="docText"><tt><i>HOURS.MICROSECONDS</I></tt></p></td></TR><tr><td class="docTableCell" align="left"><p class="docText"><tt>HOUR_SECOND</tt></p></td><TD class="docTableCell" align="left"><p class="docText"><tt><I>HOURS:MINUTES:SECONDS</i></tt></p></td></tr><TR><td class="docTableCell" align="left"><p class="docText"><tt>HOUR_MINUTE</tt></P></td><TD class="docTableCell" align="left"><p class="docText"><tt><I>HOURS:MINUTES</I></tt></p></td></tr><tr><td class="docTableCell" align="left"><p class="docText"><tt>DAY_MICROSECOND</tt></p></td><td class="docTableCell" align="left"><p class="docText"><tt><i>DAYS.MICROSECONDS</i></tt></p></td></tr><tr><TD class="docTableCell" align="left"><p class="docText"><tt>DAY_SECOND</tt></p></td><TD class="docTableCell" align="left"><p class="docText"><tt><I>DAYS HOURS:MINUTES:SECONDS</i></tt></P></td></tr><tr><TD class="docTableCell" align="left"><p class="docText"><tt>DAY_MINUTE</tt></p></td><TD class="docTableCell" align="left"><p class="docText"><tt><i>DAYS HOURS:MINUTES</i></tt></P></td></tr><tr><td class="docTableCell" align="left"><p class="docText"><tt>DAY_HOUR</tt></P></TD><td class="docTableCell" align="left"><p class="docText"><tt><i>DAYS HOURS</i></tt></p></TD></tr><TR><td class="docTableCell" align="left"><p class="docText"><tt>YEAR_MONTH</tt></P></TD><TD class="docTableCell" align="left"><p class="docText"><tt><i>YEARS-MONTHS</i></tt></p></td></tr></table></p><br>
<p class="docText">For example, if you want to calculate the date of the current day minus 12, you would write code like that in <a class="docLink" href="#learnphpmysql-CHP-8-EX-16">Example 8-16</a>.</p>
<a name="learnphpmysql-CHP-8-EX-16"></a><h5 id="title-IDAPIAMM" class="docExampleTitle">Example 8-16. Using DATE_SUB to subtract days</h5><p><table cellspacing="0" width="90%" border="1" cellpadding="5"><tr><td>

<pre>
SELECT DATE_SUB(NOW(), INTERVAL 12 DAY);
</pre><br>

</TD></tr></table></p>
<p class="docText">This returns (your time will be different based on when you run this query):</P>
<pre>
+----------------------------------+
| date_sub(NOW(), INTERVAL 12 day) |
+----------------------------------+
| 2005-11-03 04:27:09              |
+----------------------------------+
1 row in set (0.00 sec)
</pre><BR>

<p class="docText">The <tt>NOW</tt> function returns the current time; we'll discuss this and some other special date/time functions shortly. In <a class="docLink" href="#learnphpmysql-CHP-8-EX-16">Example 8-16</a>, the value after <tt>INTERVAL</tt> can be any expression that returns the format the type is expecting from <a class="docLink" href="#learnphpmysql-CHP-8-TABLE-4">Table 8-4</a>.</p>
<p class="docText">Since Version 3.23, MySQL also supports the syntax of using <tt>+</tt> and <tt>-</tt> with dates, as in <a class="docLink" href="#learnphpmysql-CHP-8-EX-17">Example 8-17</a>.</P>
<a name="learnphpmysql-CHP-8-EX-17"></a><h5 id="title-IDAZJAMM" class="docExampleTitle">Example 8-17. Using the minus operator on a date</h5><p><table cellspacing="0" width="90%" border="1" cellpadding="5"><TR><td>

<pre>
 SELECT NOW()- INTERVAL 12 DAY;
</pre><br>

</TD></tr></table></p>
<p class="docText"><a class="docLink" href="#learnphpmysql-CHP-8-EX-17">Example 8-17</a> returns:</P>
<pre>
+------------------------+
| NOW()- INTERVAL 12 DAY |
+------------------------+
| 2005-11-03 04:32:30    |
+------------------------+
1 row in set (0.01 sec)
</pre><br>

<p class="docText">It's really all the same command but with an abbreviated syntax.</p>

<a name="learnphpmysql-CHP-8-SECT-4.3.2.4"></a>
<h5 id="title-IDAOKAMM" class="docSection4Title">8.4.3.2.4. NOW function</h5>
<p class="docText">The <tt>NOW</tt> function returns the current date and time according to the setting of your computer's system date and time. So, if your computer clock is off, the data from <tt>NOW</tt> will be as well. MySQL provides several functions for returning the current date or time, or the current date and time together. <tt>CURDATE</tt> and <tt>CURRENT_DATE</tt> both return the date in <tt>'YYYY-MM-DD'</tt> format.</P>
<pre>
SELECT CURDATE();
</pre><BR>

<p class="docText">This returns:</p>
<pre>
+------------+
| CURDATE()  |
+------------+
| 2005-11-15 |
+------------+
1 row in set (0.00 sec)
</pre><br>

<p class="docText">Use <tt>CURTIME</tt> or <tt>CURRENT_TIME</tt> to return the current time in the format <tt>'HH:MM:SS'</tt>:</p>
<pre>
SELECT CURTIME();
</pre><br>

<p class="docText">Computer setting for date and time returns:</P>
<pre>
+-----------+
| CURTIME() |
+-----------+
| 04:44:50  |
+-----------+
1 row in set (0.00 sec)
</pre><br>

<p class="docText">In addition to the <tt>NOW</tt> function, you can use <tt>SYSDATE</tt> and <tt>CURRENT_TIMESTAMP</tt> to return the current date and time in the <tt>'YYYY-MM-DD HH:MM:SS'</tt> format:</P>
<pre>
SELECT SYSDATE();
</pre><br>

<p class="docText">Military formatted data is returned:</P>
<pre>
+---------------------+
| SYSDATE()           |
+---------------------+
| 2005-11-15 04:45:14 |
+---------------------+
1 row in set (0.00 sec)
</pre><BR>

<p class="docText">Last but not least, MySQL provides the ability to display dates and times in a variety of formats.</P>

<a name="learnphpmysql-CHP-8-SECT-4.3.2.5"></a>
<h5 id="title-IDAKMAMM" class="docSection4Title">8.4.3.2.5. Formatting for display</h5>
<p class="docText">To display a date in a custom format, use the <tt>DATE_FORMAT</tt> function. It takes a date or timestamp as its input and a format string. In <a class="docLink" href="#learnphpmysql-CHP-8-TABLE-5">Table 8-5</a>, the format strings are shown.</p>
<a name="learnphpmysql-CHP-8-TABLE-5"></a><p><table cellspacing="0" FRAME="hsides" RULES="all" cellpadding="4" width="100%"><caption><h5 class="docTableTitle">Table 8-5. Format strings for DATE_FORMAT</h5></caption><colgroup span="3"><col><col><col></colgroup><thead><tr><th class="thead" scope="col" align="left"><p class="docText">Format</p></th><th class="thead" scope="col" align="left"><p class="docText">Type</p></th><th class="thead" scope="col" align="left"><p class="docText">Example</p></th></tr></thead><tr><td class="docTableCell" align="left"><p class="docText"><tt>%M</tt></p></TD><td class="docTableCell" align="left"><p class="docText">Month name</p></TD><TD class="docTableCell" align="left"><p class="docText">January-December</p></TD></tr><tr><td class="docTableCell" align="left"><p class="docText"><tt>%W</tt></P></td><td class="docTableCell" align="left"><p class="docText">Weekday name</P></td><td class="docTableCell" align="left"><p class="docText">Sunday-Saturday</P></td></tr><tr><td class="docTableCell" align="left"><p class="docText"><tt>%D</tt></P></TD><td class="docTableCell" align="left"><p class="docText">Day of the month with English suffix</p></td><td class="docTableCell" align="left"><p class="docText">0th, 1st, 2nd, 3rd</P></td></TR><tr><TD class="docTableCell" align="left"><p class="docText"><tt>%Y</tt></P></TD><td class="docTableCell" align="left"><p class="docText">Year, numeric, four digits</p></td><td class="docTableCell" align="left"><p class="docText">2005</p></td></tr><tr><td class="docTableCell" align="left"><p class="docText"><tt>%y</tt></p></td><td class="docTableCell" align="left"><p class="docText">Year, numeric, two digits</p></td><TD class="docTableCell" align="left"><p class="docText">05</p></td></TR><TR><td class="docTableCell" align="left"><p class="docText"><tt>%X</tt></P></td><td class="docTableCell" align="left"><p class="docText">Year for the week where Sunday is the first day of the week, numeric, four digits; used with <tt>%V</tt></p></TD><td class="docTableCell">&nbsp;</td></TR><tr><td class="docTableCell" align="left"><p class="docText"><tt>%x</tt></P></td><td class="docTableCell" align="left"><p class="docText">Year for the week, where Monday is the first day of the week, numeric, four digits; used with <tt>%v</tt></p></td><TD class="docTableCell">&nbsp;</TD></tr><tr><td class="docTableCell" align="left"><p class="docText"><tt>%a</tt></p></TD><td class="docTableCell" align="left"><p class="docText">Abbreviated weekday name</P></td><TD class="docTableCell" align="left"><p class="docText">Sun, Sat</P></TD></tr><tr><td class="docTableCell" align="left"><p class="docText"><tt>%e</tt></p></td><td class="docTableCell" align="left"><p class="docText">Day of the month, numeric leading zero</p></td><td class="docTableCell" align="left"><p class="docText">00-31</p></td></tr><tr><td class="docTableCell" align="left"><p class="docText"><tt>%m</tt></P></td><td class="docTableCell" align="left"><p class="docText">Day of the month, numeric</P></TD><td class="docTableCell" align="left"><p class="docText">0-31</P></td></tr><tr><TD class="docTableCell" align="left"><p class="docText"><tt>%c</tt></p></td><TD class="docTableCell" align="left"><p class="docText">Month, numeric leading zero</p></td><TD class="docTableCell" align="left"><p class="docText">00-12</p></td></tr><tr><TD class="docTableCell" align="left"><p class="docText"><tt>%b</tt></P></td><td class="docTableCell" align="left"><p class="docText">Month, numeric</p></td><TD class="docTableCell" align="left"><p class="docText">0-12</p></TD></tr><TR><TD class="docTableCell" align="left"><p class="docText"><tt>%m</tt></P></td><td class="docTableCell" align="left"><p class="docText">Abbreviated month name</p></td><td class="docTableCell" align="left"><p class="docText">Jan, Dec</p></td></tr><tr><td class="docTableCell" align="left"><p class="docText"><tt>%b</tt></p></td><td class="docTableCell" align="left"><p class="docText">Day of year</p></TD><td class="docTableCell" align="left"><p class="docText">001, 366</p></TD></TR><tr><TD class="docTableCell" align="left"><p class="docText"><tt>%j</tt></p></td><td class="docTableCell" align="left"><p class="docText">Hour</P></td><td class="docTableCell" align="left"><p class="docText">00-23</P></td></tr><TR><td class="docTableCell" align="left"><p class="docText"><tt>%H</tt></p></td><td class="docTableCell" align="left"><p class="docText">Hour</P></TD><td class="docTableCell" align="left"><p class="docText">0-23</p></td></tr><TR><td class="docTableCell" align="left"><p class="docText"><tt>%k</tt></P></td><TD class="docTableCell" align="left"><p class="docText">Hour</P></TD><td class="docTableCell" align="left"><p class="docText">01-12</p></td></tr><tr><td class="docTableCell" align="left"><p class="docText"><tt>%h</tt></p></td><td class="docTableCell" align="left"><p class="docText">Hour</p></td><td class="docTableCell" align="left"><p class="docText">01-12</p></td></TR><tr><td class="docTableCell" align="left"><p class="docText"><tt>%l</tt></P></TD><td class="docTableCell" align="left"><p class="docText">Hour</P></td><td class="docTableCell" align="left"><p class="docText">1-12</p></TD></tr><tr><TD class="docTableCell" align="left"><p class="docText"><tt>%I</tt></p></td><TD class="docTableCell" align="left"><p class="docText">Minutes, numeric</p></td><td class="docTableCell" align="left"><p class="docText">00-59</p></TD></TR><tr><td class="docTableCell" align="left"><p class="docText"><tt>%r</tt></p></td><TD class="docTableCell" align="left"><p class="docText">12-hour (<tt>hh:mm:ss</tt> followed by AM or PM)</p></TD><td class="docTableCell">&nbsp;</TD></TR><TR><td class="docTableCell" align="left"><p class="docText"><tt>%T</tt></p></td><td class="docTableCell" align="left"><p class="docText">24-hour (<tt>hh:mm:ss</tt>)</p></td><td class="docTableCell">&nbsp;</td></tr><tr><td class="docTableCell" align="left"><p class="docText"><tt>%S</tt></p></td><td class="docTableCell" align="left"><p class="docText">Seconds</P></td><td class="docTableCell" align="left"><p class="docText">00-59</P></TD></tr><TR><td class="docTableCell" align="left"><p class="docText"><tt>%s</tt></p></td><TD class="docTableCell" align="left"><p class="docText">Seconds</p></td><TD class="docTableCell" align="left"><p class="docText">00-59</p></td></TR><tr><td class="docTableCell" align="left"><p class="docText"><tt>%f</tt></p></td><TD class="docTableCell" align="left"><p class="docText">Microseconds</P></td><td class="docTableCell" align="left"><p class="docText">000000-999999</p></td></TR><tr><TD class="docTableCell" align="left"><p class="docText"><tt>%p</tt></p></TD><TD class="docTableCell" align="left"><p class="docText">AM or PM</P></td><td class="docTableCell">&nbsp;</td></tr><tr><td class="docTableCell" align="left"><p class="docText"><tt>%w</tt></p></td><td class="docTableCell" align="left"><p class="docText">Day of the week (0=Sunday-6=Saturday)</p></td><td class="docTableCell">&nbsp;</td></tr><TR><td class="docTableCell" align="left"><p class="docText"><tt>%U</tt></p></TD><TD class="docTableCell" align="left"><p class="docText">Week (00-53), where Sunday is the first day of the week</p></TD><td class="docTableCell">&nbsp;</td></tr><TR><td class="docTableCell" align="left"><p class="docText"><tt>%V</tt></p></TD><td class="docTableCell" align="left"><p class="docText">Week (00-53), where Monday is the first day of the week</p></TD><td class="docTableCell">&nbsp;</td></tr><tr><TD class="docTableCell" align="left"><p class="docText"><tt>%v</tt></P></td><td class="docTableCell" align="left"><p class="docText">Week (01-53), where Monday is the first day of the week; used with <tt>%x</tt></p></td><TD class="docTableCell">&nbsp;</td></TR><tr><TD class="docTableCell" align="left"><p class="docText"><tt>%%</tt></P></TD><td class="docTableCell" align="left"><p class="docText">A literal <tt>%</tt></p></td><td class="docTableCell" align="left"><p class="docText"><tt>%</tt></p></td></tr></table></p><br>
<p class="docText">If you use any other characters in the format string, they appear as they are, as shown in <a class="docLink" href="#learnphpmysql-CHP-8-EX-18">Example 8-18</a>.</p>
<a name="learnphpmysql-CHP-8-EX-18"></a><h5 id="title-IDAD4AMM" class="docExampleTitle">Example 8-18. Using DATE_FORMAT with a string to place colons between the segments</h5><p><table cellspacing="0" width="90%" border="1" cellpadding="5"><tr><TD>

<pre>
SELECT DATE_FORMAT('2006-12-24 09:09:23', '%h:%i:%s');
</pre><br>

</td></TR></table></P>
<p class="docText">Adding colons displays:</p>
<pre>
+------------------------------------------------+
| DATE_FORMAT('2006-12-24 09:09:23', '%h:%i:%s') |
+------------------------------------------------+
| 09:09:23                                       |
+------------------------------------------------+
1 row in set (0.01 sec)
</pre><BR>

<p class="docText">At this point, all the basics have been covered. In our next chapter, we'll walk through using PHP to connect and work with MySQL data.</p>




<a href="11011536.html"><img src="images/pixel.jpg" alt="" width="1" height="1" border="0"></a></TD></TR></table>
<br>
<table width="100%" border="0" cellspacing="0" cellpadding="0">
<tr><td><div STYLE="MARGIN-LEFT: 0.15in;">
<a href=learnphpmysql-CHP-8-SECT-3.html><img src="images/prev.gif" width="60" height="17" border="0" align="absmiddle" alt="Previous Page"></a>
<td align="right"><div STYLE="MARGIN-LEFT: 0.15in;">
<a href=learnphpmysql-CHP-8-SECT-5.html><img src="images/next.gif" width="60" height="17" border="0" align="absmiddle" alt="Next Page"></a>
</div></td></tr></table>
</body></html>
